#!/usr/bin/perl
#===============================================================================
# Copyright (c) 2009,
# All rights reserved.
# 摘    要 ：一些常用的时间处理函数
# 作    者 ：2009-12-15 xingwang
# 版    本 ：0.01
#===============================================================================
use strict;
use warnings;
use HTTP::Date;
use utf8;
use Encode;
#use utf8::all;
use String::MkPasswd qw(mkpasswd);
#binmode STDOUT,":utf8";
use File::Spec;
use File::Copy;
use File::Path qw(mkpath rmtree);
#use File::Cat;
use Oracle::SQLLoader2 qw($CHAR $INT $DECIMAL $DATE);
use DBI;
#use Oracle::Sqlldr;
use LWP::UserAgent;

# ------------------------------
# &printlog
# 记录日志进文件，且同时屏显
# ------------------------------
sub printlog
{
     my $file = shift;
     my $msg = shift ;
	 #$msg = Encode::decode("utf8", $msg);
     open(FILE_LOG,">>$file");
     print FILE_LOG $msg ;# 写入文件，主程序须要open FILE_LOG文件句柄
     close(FILE_LOG);
     print $msg;                #打印至屏幕
          
}
# ------------------------------
# &printcfg
# 记录日志进文件，且同时屏显
# ------------------------------

sub printcfg
{
     my $file = shift;
     my $msg = shift ;
	 #$msg = Encode::decode("utf8", $msg);
     open(FILE_LOG,">$file");
     print FILE_LOG $msg ;# 写入文件，主程序须要open FILE_LOG文件句柄
     close(FILE_LOG);
     #print $msg;                #打印至屏幕
          
}
# ------------------------------
# &writecache
# 写文件不回显
# ------------------------------
sub writecache
{
     my $file = shift;
     my $msg = shift ;
	 #$msg = Encode::decode("utf8", $msg);
     open(FILE_LOG,">>$file");
     print FILE_LOG $msg ;# 写入文件，主程序须要open FILE_LOG文件句柄
     close(FILE_LOG);
#     print $msg;                #打印至屏幕
}

# ------------------------------
# &querycachefile
# 查询缓存目录下的缓存文件，处理本通道下的缓存文件，大于15秒的打包
# ------------------------------
sub querycachefile
{
	my $handle = shift;
	my %cache = %$handle;
	my ($fullfile,$filetmp,$cttime,$ss);
	my %fileinfo;
	my $nowsrl = &nowsrl;
	opendir(DIR, "$cache{cachedir}") || die "Cannot open dir : $!";
	foreach $filetmp (readdir(DIR)) {
		#循环文件夹内的文件，判断是否本通道的，如果是本通道的且创建时间为15s以前的，则重命名打包
		$fullfile = "$cache{cachedir}/$filetmp";
		if(-f $fullfile) {
			$cttime=(stat $fullfile)[10] or die "Cannot open file : $!";
			$ss = time()-$cttime;
			
			#print $ss.'_'.$cttime."\r\n";
			if ($ss >= 15) {
				#分割文件名
				($fileinfo{platid},$fileinfo{pno},$fileinfo{create}) = split(/_/,$filetmp);
				$cache{rootplatdir} = $cache{rootdir}.$fileinfo{platid}.'/'.substr($nowsrl,0,4).'/'.substr($nowsrl,0,6).'/'.substr($nowsrl,0,8).'/';
				$cache{mvfile} = $cache{rootplatdir}.'file_'.$fileinfo{platid}.'_'.$fileinfo{pno}.'_'.$nowsrl.'_'.&mkrandom.'.txt';
				&printlog($fileinfo{file_log},&nowtime." Cache File Move to $cache{mvfile} \r\n");
				if (-e $cache{rootplatdir}) {
					move $fullfile,$cache{mvfile};
				} else {
					#print $cache{rootdirspc}."\r\n";
					mkpath($cache{rootplatdir},0,0775);
					move $fullfile,$cache{mvfile};
				} 
			}
		}
	}
	closedir(DIR);
	
	
}


# ------------------------------
# 检查目录是否为空文件夹。如果是空的且创建时间小于当天的则删除
# &checkdir
# 
# ------------------------------
sub checkdir {
	my $handle = shift;
	my $logger = shift;
	my $tabtmpdir = shift;
	my $tabcfg = shift;
	my $dbcfg = shift;
	my %tabcfg = %{$tabcfg};
	my (@fileinfo,%dircfg);
	my (%filecache);
	my (%filelist,$i);
	$dircfg{scandir} = $handle."recv/";
	$dircfg{hisdir} = $handle."recvhis/";
	$i = 0;
	opendir(DIR, "$dircfg{scandir}") || die "Cannot open dir : $dircfg{scandir} \n";
	foreach my $filetmp (readdir(DIR)) {
		if ($filetmp eq '.' || $filetmp eq '..') {
            next;
        }
		#循环文件夹内的文件，判断是否本通道的，如果是本通道的且创建时间为15s以前的，则重命名打包
		$dircfg{fullfile} = $dircfg{scandir}.$filetmp;
		@fileinfo = split(/_/,$filetmp);
		if (@fileinfo ge 2) {
			&printlog($logger,&nowtime." File $dircfg{fullfile}");
			$dircfg{cttime}=(stat $dircfg{fullfile})[10] or die "Cannot open file : $!";
			$dircfg{ss} = time()-$dircfg{cttime};
			if ($dircfg{ss} > 300) {
				&printlog($logger," Deal \r\n");
				#修改时间超过300s
				$dircfg{tabtmp} = $fileinfo[0];
				$filecache{$dircfg{tabtmp}}{name} = $filetmp;
				$filecache{$dircfg{tabtmp}}{sourcefile} = $dircfg{fullfile};
				if (! $filecache{$dircfg{tabtmp}}{moveto}) {
					#缓存标记未定义时定义
					$filecache{$dircfg{tabtmp}}{filehandle} = "$dircfg{tabtmp}"."_".&nowsrl;
					$filecache{$dircfg{tabtmp}}{filename} = $filecache{$dircfg{tabtmp}}{filehandle}.'.txt';
					$filecache{$dircfg{tabtmp}}{moveto} = $tabtmpdir.$filecache{$dircfg{tabtmp}}{filename};
					&printlog($logger,&nowtime." Create Moveto $filecache{$dircfg{tabtmp}}{moveto} \r\n");
				}
				
				try {
					#文件移动到历史目录 先移动再添加文件，移动失败的不添加
					$dircfg{hisdirtmp} = $dircfg{hisdir}.substr(&nowsrl,0,4).'/'.substr(&nowsrl,0,6).'/'.substr(&nowsrl,0,8).'/';
					$dircfg{hisfile} = $dircfg{hisdirtmp}.$filetmp;
					&printlog($logger,&nowtime." Move File $dircfg{fullfile} --->>> $dircfg{hisfile} ");
					if (-e $dircfg{hisdirtmp}) {
						move $dircfg{fullfile},$dircfg{hisfile};
						&printlog($logger," --->>> Succ \r\n");
					} else {
						#print $cache{rootdirspc}."\r\n";
						mkpath($dircfg{hisdirtmp},0,0777);
						move $dircfg{fullfile},$dircfg{hisfile};
						&printlog($logger," --->>> Succ \r\n");
					} 
				} catch {
					#move failed
					&printlog($logger,&nowtime." Move Failed $dircfg{fullfile} --->>> $dircfg{hisfile} ");
					next;
				};	
				&printlog($logger,&nowtime." Append File $dircfg{hisfile} --->>> $filecache{$dircfg{tabtmp}}{moveto}");
				# 合并文件
				open(FILE_DATA,"<",$dircfg{hisfile}) || die "cannot open the file: $dircfg{hisfile} \n";
				open(FILE_MOVE,">>$filecache{$dircfg{tabtmp}}{moveto}");
				while (my $content = <FILE_DATA>){
					#$content;
					chomp $content; 
					if ($tabcfg{$dircfg{tabtmp}}{addcolumn}) {
						$content = &addcolumn(substr($content,0,10)).$content.'$$'.$filecache{$dircfg{tabtmp}}{filename}."\n";
					} else {
						$content = $content.'$$'.$filecache{$dircfg{tabtmp}}{filename}."\n";
					}
					print FILE_MOVE $content ;# 写入文件，主程序须要open FILE_LOG文件句柄
				}
				close FILE_DATA;
				close FILE_MOVE;
				&printlog($logger," --->>> Succ \r\n");
			} else {
				&printlog($logger," Skip \r\n");
			}
		}
		#每次处理100个文件
		$i ++;
		if ($i >= 30 ) {
			last;
		}
	}
	
	#$platcfg{$fileinfo[1]}{filecnt} = $filecnt;
	#&printlog($logger,&nowtime." Check $handle : $filecnt files ,Skip \n");
	closedir(DIR);
	
	#SQLLDR
	foreach my $key ( keys %filecache ) {
		try {
			func_sqlldr($filecache{$key}{filehandle},$tabcfg{$key},$dbcfg,$logger,$tabtmpdir);
		} catch {
			&printlog($logger,&nowtime." Error $filecache{$key}{moveto} \r\n ");
		};
	}
}


# ------------------------------
# ORACLE SQLLDR。
# &func_sqlldr
# 
# ------------------------------
sub func_sqlldr 
{
	my $filehandle = shift;
	my $tabcfg = shift;
	my $dbcfg = shift;
	my $logger = shift;
	my $tabtmpdir = shift;
	my $etlcfg = shift;
	my $sqlldr;
	my %oracfg;
	my %dbcfg = %{$dbcfg};
	my %tabcfg = %{$tabcfg};
	my %etlcfg = %{$etlcfg};
	my @sortarr;
	
	$oracfg{file} = $tabtmpdir.$filehandle.'.txt';
	$oracfg{ctl} = $oracfg{file}.'.ctl';
	$oracfg{log} = $oracfg{file}.'.log';
	$oracfg{dis} = $oracfg{file}.'.dis';
	$oracfg{bad} = $oracfg{file}.'.bad';
	$oracfg{table} = $tabcfg{name};
	$oracfg{field} = $tabcfg{field};
	$etlcfg{relaodfile} = $etlcfg{reloaddir}.$filehandle.'.txt';

#print Dumper(\%oracfg)."\r\n";
	$sqlldr = new Oracle::SQLLoader2(
                              infile => $oracfg{file},
                              terminated_by => $oracfg{field},
                              username => $dbcfg{usrename},
                              password => $dbcfg{password},
                              sid => $dbcfg{sid},
                              control_file => $oracfg{ctl},
                              badfile => $oracfg{bad},
                              discardfile => $oracfg{dis},
                              logfile => $oracfg{log},
                              cleanup => '',
                              errors => 10000000,
                              parallel => 'false',
                             );
	$sqlldr->addTable(table_name => $oracfg{table});
	
	
	foreach my $key ( keys %{$tabcfg{column}} ) {
		my $subhash = $tabcfg{column}{$key};
		my %subhash = %{$subhash};
		foreach my $key2 ( keys %subhash ) {
			my $subhash2 = $subhash{$key2};
			#my %subhash2 = %{$subhash2};
			#print $subhash{pm}." --> ".$key2." --> ".$subhash2."\r\n";
			$sortarr[$subhash{pm}]{$key2} = $subhash2;
		}
	}
	#print Dumper(\@sortarr)."\r\n";
	foreach my $subhash (@sortarr) {
		if ($subhash) {
			my %subhash = %{$subhash};
			if ($subhash{type} eq "DATE") {
				$sqlldr->addColumn(column_name => $subhash{name},
						   		   column_type => $DATE,
	                  	   		   date_format => "$subhash{lx}"
	                  	   		   );
			} elsif ($subhash{type} eq "CHAR") {
				$sqlldr->addColumn(column_name => $subhash{name},
						   		   column_type => $CHAR
						   		   );
			} elsif ($subhash{type} eq "INT") {
				$sqlldr->addColumn(column_name => $subhash{name},
						   		   column_type => $INT
						   		   );
			}
		}
	}
	
	#$sqlldr->executeLoader() || warn "Problem executing sqlldr: $@\n";
	func_executeLoader($sqlldr,$logger,\%dbcfg) || warn "Problem executing sqlldr: $@\n";
	# stats
	my ($skipped,$read,$rejects,$discards,$loads,$beginTS,$endTS,$runtimeSecs,$secsOnCpu,$errs);
	$skipped = $sqlldr->getNumberSkipped();
	$read = $sqlldr->getNumberRead();
	$rejects = $sqlldr->getNumberRejected();
	$discards = $sqlldr->getNumberDiscarded();
	$loads = $sqlldr->getNumberLoaded();
	$beginTS = $sqlldr->getLoadBegin();
	$endTS = $sqlldr->getLoadEnd();
	$runtimeSecs = $sqlldr->getElapsedSeconds();
	$secsOnCpu = $sqlldr->getCpuSeconds();
	&printlog($logger,&nowtime." $dbcfg{oratype} Sqlldr info: skipped:$skipped read:$read rejects:$rejects discards:$discards loads:$loads beginTS:$beginTS beginTS:$beginTS endTS:$endTS runtimeSecs:$runtimeSecs secsOnCpu:$secsOnCpu\r\n");
	

#  return 0 unless $sqlldr->executeLoader();
	if ($read && $loads && $read>0 && $loads > 0 && $read == $loads) {
		&printlog($logger,&nowtime." Load Succ !~~ \r\n");
		if ($etlcfg{send}) {
			&printlog($logger,&nowtime." Ready to Send $etlcfg{send} ");
			open(FILE_DATA,"<",$oracfg{file}) || die "cannot open the file: $oracfg{file} \n";
			my $str='';
			while (my $content = <FILE_DATA>){
				#$content;
				chomp $content;
				$str .= $content.'~~~~';
			}
			close FILE_DATA;
			$str = substr($str,0,length($str)-4);
			&printlog($logger," ---> Sending...");
			my $postres = &func_post($etlcfg{send},$str);
			&printlog($logger," ---> $postres \r\n");
			if ($postres eq 'ok') {
				&printlog($logger,&nowtime." Send Succ!~ ");
				#unlink($oracfg{file});
			} else {
				&printlog($logger,&nowtime." Send Error !~ ");
			}
		}
		#else {
		#	&printlog($logger,&nowtime." Rremove File $oracfg{file}");
			#unlink($oracfg{file});
		#}
		#unlink($oracfg{file});
		#unlink($oracfg{ctl});
		#unlink($oracfg{log});
		#&printlog($logger," --->>> succ !~ \r\n");
		return 1;
	} else {
		&printlog($logger,&nowtime." Load Failed !~~ $oracfg{file} \r\n");
		#sendmail
		&smtp_mailto('wujiming@motinghl.com','SQLLDR ERROR',"Project:LoadFile<br> CMD:sqlldr control=$oracfg{ctl} userid=$dbcfg{username}\/$dbcfg{password}\@$dbcfg{sid} log=$oracfg{log}");
   		return 0;
	}
}



################################################################################
sub func_executeLoader {
  my $self = shift;
  my $logger = shift;
  my $dbcfg = shift;
  my %dbcfg = %{$dbcfg};
	
  #$self->_initDefaults();
  $self->generateControlfile();
#  if ($self->{'_OSTYPE'} ne 'WIN') {
  my $exe = $ENV{'ORACLE_HOME'}."/bin/sqlldr";
  my $cmd = "$exe control=$self->{'_control_file'} ".
            "userid=$dbcfg{username}".'/'."$dbcfg{password}".'@'."$dbcfg{sid} ".
            "log=$self->{'_cfg_global'}{'logfile'} 2>&1";
  #print $cmd."\r\n";
  &printlog($logger,&nowtime." Cmd --> $cmd \r\n");
  my $output = `$cmd`;
  my $exitval = $? ;

  $self->checkLogfile();
  return !$exitval;
}


# ------------------------------
# get接口发送数据
# &func_get
#
# ------------------------------
sub func_get {
	my $url = shift;
	my ($req,$res,$str);
	try {
			my $ua = LWP::UserAgent->new;
			#$ua->agent("$0/0.1 " . $ua->agent);
			#$ua->agent("Mozilla/5.0 (Linux; U; Android 4.0.3; zh-cn; U8817 Build/MocorDroid4.0.3) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1");
			#$ua->env_proxy;
			$req = HTTP::Request->new(GET => $url);
			#$req->header('Accept' => 'text/html');
			$res = $ua->request($req);
			if ($res->is_success) {
				$str = $res->content;
			}	else {
				$str = $res->status_line;
			}
		}
		catch {
				$str = "error";
			};
	return $str;
}

# ------------------------------
# post接口发送数据
# &func_post
#
# ------------------------------
sub func_post {
	my ($url,$content) = @_;
	my ($req,$res,$str);

	try {
			if ($url) {
				my $ua = LWP::UserAgent->new;
				#$ua->agent("$0/0.1 " . $ua->agent);
				#$ua->agent("Mozilla/5.0 (Linux; U; Android 4.0.3; zh-cn; U8817 Build/MocorDroid4.0.3) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1");
				#$ua->env_proxy;
				$req = HTTP::Request->new(POST => $url);
				#$req->header('Accept' => 'text/html');
				$req->content($content);
				$res = $ua->request($req);
				if ($res->is_success) {
					$str = $res->content;
				}	else {
					$str = $res->status_line;
				}
			} else {
				$str = "no url";
			}
		} catch {
				$str = "error";
			};
	return $str;
}

# ------------------------------
# MYSQL SQLLDR。
# &func_mysqlldr
#
# ------------------------------
sub func_mysqlldr
{
	my $filehandle = shift;
	my $tabcfg = shift;
	my $dbcfg = shift;
	my $logger = shift;
	my $tabtmpdir = shift;
	my $etlcfg = shift;

	my %oracfg;
	my %dbcfg = %{$dbcfg};
	my %tabcfg = %{$tabcfg};
	my %etlcfg = %{$etlcfg};
	my @sortarr;

	$oracfg{file} = $tabtmpdir.$filehandle.'.txt';
	$oracfg{table} = $tabcfg{name};
	if ($tabcfg{field} =~ /^\d+$/) {
		#$$为特殊字符表示系统进程号为数字因此如果为数字的就转换
		$oracfg{field} = "\\\$\\\$";
	} else {
		$oracfg{field} = $tabcfg{field};
	}
	$etlcfg{relaodfile} = $etlcfg{reloaddir}.$filehandle.'.txt';


	my $dbh = DBI->connect("DBI:mysql:database=$dbcfg{db};host=$dbcfg{ip}:$dbcfg{db_port}", "$dbcfg{username}", "$dbcfg{password}", {'RaiseError' => 1});
	my $cmd = "LOAD DATA LOCAL INFILE \"$oracfg{file}\" INTO TABLE $oracfg{table} FIELDS TERMINATED BY \"$oracfg{field}\"";
	&printlog($logger,&nowtime." $dbcfg{oratype} Cmd --> $cmd \r\n");
	my $rows = $dbh->do($cmd);
	&printlog($logger,&nowtime." Sqlldr info: loads:$rows ");
	if ($rows>0) {
		&printlog($logger," --->>> succ !~ \r\n");
		return 1;
	} else {
		&printlog($logger," Load Failed !~~ $oracfg{file} \r\n");
		#sendmail
		&smtp_mailto('wujiming@motinghl.com','SQLLDR ERROR',"Project:LoadFile<br> DB:$dbcfg{db} <br> CMD:$cmd");
		return 0;
	}
}
# ----------------------------------------------------
# &addcolumn()
# 格式化日期
# 输入的格式：
# yyyy-mm-dd、yyyy-m-d、yyyymmdd、yyyy/mm/dd、
# yyyy/m/d、yyyy-mm、yyyy-m、yyyymm、yyyy/mm、yyyy/m
# ----------------------------------------------------
sub addcolumn {
	my $date = shift;
        $date =~ /^(\d{4})[-\/](\d{2}|\d)[-\/](\d{2}|\d)$/g; # yyyy-mm-dd yyyy/mm/dd
        die "Format Date Erro:The date you input( $date ) is not correct." unless ( $1 or $2 );
        
        return $2.'$$'.$3.'$$'.sprintf("%04d%02d%02d",$1,$2,$3).'$$';
=a       
        
        
        if ( $type eq 'yyyymm' ) {
        	return sprintf("%04d%02d",$1,$2);
        } elsif ( $type eq 'yyyymmdd' and $3) {
                return sprintf("%04d%02d%02d",$1,$2,$3);
        } elsif ( $type eq 'yyyy-mm-dd' and $3) {
                return sprintf("%04d-%02d-%02d",$1,$2,$3);
        } elsif ( $type eq 'yyyy-mm') {
                return sprintf("%04d-%02d",$1,$2);
        } elsif ( $type eq 'alldays' or $type eq 'endday' ) {
                my $hs_monthDays = {'01'=>'31','03'=>'31','05'=>'31','07'=>'31','08'=>'31','10'=>'31','12'=>'31','04'=>'30','06'=>'30','09'=>'30','11'=>'30'};
                $hs_monthDays->{'02'} = ($1%4==0&&$1%100!=0)||$1%400==0 ? 29 : 28;
                return $hs_monthDays->{$2};
        } else {
                die "Format Date Erro:Return date ( Date = $date ,Type = $type ) is not correct."
        }
=cut     
        
}
# ------------------------------
# func_sqlldr之后的。
# &func_cleanup
# 
# ------------------------------
sub func_cleanup 
{
0;
}
# ------------------------------
# 检查目录是否为空文件夹。
# &is_emptydir
# 
# ------------------------------
sub is_emptydir {
    opendir DIR, shift or die "$!\n";
    grep !/^\.\.?$/, readdir DIR;
    #closedir(DIR);
}

# ------------------------------
# &utf8_to_gbk
# 将utf8 编码转成gbk编码
# ------------------------------
#sub utf8_to_gbk
#{
#    my $msg = shift;
#    my $isutf8;
#    $isutf8 = Encode::is_utf8($msg);
#    if ($isutf8){
#        $msg = encode("gbk", $msg);
#    }
#    return $msg;
#}


#------------------------------
# &splitstr
# 将字符串转换成数组
#------------------------------
sub splitstr
{
  my $str = shift;
  my $sp  = shift;
  my $pos = shift;
  my @d = split(/$sp/,$str);
  return $d[$pos];
}

#----------------------------
# &mkrandom
# 生成一个不重复随机数
#----------------------------
sub mkrandom
{
  return mkpasswd(-length=>8,-minnum=>2,-minlower=>2,-minspecial=>0,);
  #print mkpasswd(-length=>4,-minnum=>2,-minlower=>2,);

}


#----------------------------
# &ftp_getlist
# ftp方法 连接远程服务器，获取文件
#----------------------------
sub ftp_getlist
{
	
	
}


#----------------------------
# &sftp_getlist
# sftp方法
#----------------------------
sub sftp_getlist
{
	#my $sftp = Net::SFTP->new($host, %args);
	
	
}

#----------------------------
# &sftp_getlist
# sftp方法
#----------------------------
sub sftp_rm
{
	
	
}

1;




